
package Database;
import java.sql.*;
import java.util.Vector;
public class Stock {
    
    
    public static  int sumProduct()
    {
        int a =0 ;
        try {
            Connection con = ConecttoDatabase.getConnection();
            String query = "select Sum(Quantily) from Product";
            Statement st = con.createStatement();
            ResultSet rs = st.executeQuery(query);
            while (rs.next()) {                
                a += rs.getInt(1);
            }
            
        } catch (Exception e) {
        }
        
        return  a;
    }
    
    
    public static int totalProductPurchar(String time , String moth , String masp)
    {
        int a = 0;
        try {
            Connection con = ConecttoDatabase.getConnection();
            String query = "select sum(Quantily) from  DetailPurchaseBill d ,PurchaseBill p where p.BillId = d.BillId and YEAR(BuildlDate) ='"+time+"' and MONTH(BuildlDate) = '"+moth+"' and d.ProductId='"+masp+"' ";
            Statement st = con.createStatement();
            ResultSet rs = st.executeQuery(query);
            while (rs.next()) {                
                a += rs.getInt(1);
            }
        } catch (Exception e) {
            System.out.println(e);
        }
        return a;
    }
    
    
    public static  Vector ListProduct()
    {
        Vector pr = new Vector();
        try {
            Connection con = ConecttoDatabase.getConnection();
            String query =  "select ProductName from Product";
            Statement st = con.createStatement();
            ResultSet rs = st.executeQuery(query);
            while (rs.next()) {                
                Vector vt = new Vector();
                pr.add(rs.getString("ProductName"));
                
            }
        } catch (Exception e) {
        pr = null;
            System.out.println("loi o day:"+e);
        }
        
        return pr;
    }  
    public static  String IdProduct(String nameProduct)
    {
        Vector v = new Vector();
        String idp = "";
        try {
            Connection con = ConecttoDatabase.getConnection();
            String query = "select p.ProductId from Product p where p.ProductName='"+nameProduct+"' ";
            Statement st = con.createStatement();
            ResultSet rs = st.executeQuery(query);
            while (rs.next()) {                
                idp = rs.getString("ProductId");
            }
        } catch (Exception e) {
            v=null;
            System.out.println(e);
        }
        return  idp;
    }
   
    public static Vector History(int id)
    {
        Vector vt = new Vector();
        try {
            Connection con =  ConecttoDatabase.getConnection();
            String query = "select pu.BuildlDate , p.ProductName , v.VenderName , d.Quantily ,d.Price, (d.Price * d.Quantily)as[thanhtien]"
                    + " from DetailPurchaseBill d, Product p , Vender v ,PurchaseBill pu "
                    + "where d.ProductId = p.ProductId and pu.BillId = d.BillId and  v.VenderId = pu.VendorId and p.ProductId='"+id+"' order by pu.BuildlDate DESC";
            Statement st = con.createStatement();
            ResultSet rs = st.executeQuery(query);
            while (rs.next()) {  
                Vector v = new Vector();
                v.add(rs.getString("BuildlDate"));
                v.add(rs.getString("ProductName"));
                v.add(rs.getString("VenderName"));
                v.add(rs.getString("Quantily"));
                v.add(rs.getString("Price"));
                v.add(rs.getString("thanhtien"));
                vt.add(v);
            }
        } catch (Exception e) {
            vt=  null;
            System.out.println("bi loi o day: "+e);
        }
        return vt;
    }
    public static  int sumProductSales(String id , String date)
    {
        int sum = 0;
        try {
            Connection con = ConecttoDatabase.getConnection();
            String query = "select SUM(Quantity) from DetailSalesBill d , SalesBill s ,Product p where d.BillId=s.BillId and p.ProductId = d.ProductId and d.ProductId='"+id+"' and s.BuildDate >= '"+date+"'";
            Statement st = con.createStatement();
             ResultSet rs = st.executeQuery(query);
             while (rs.next()) {                
                sum += rs.getInt(1);
            }
        } catch (Exception e) {
            sum = 0;
            System.out.println(e);
        }
        return  sum;
    }
    
}
